using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for DM_USER.
    /// </summary>
    public class DM_USERDao
    {
        private string _SQLSelectByKey;
        private string _SQLSelectByKeyUserName;
        private string _SQLUpdate;
        private string _SQLInsert;
        private string _SQLDelete;
        private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion

        #region Constructor

        public DM_USERDao()
        {
            _dbConnection = StaticVar.ServerConnection;
            InitSqlQuery();
        }

        public DM_USERDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
            _SQLSelectByKeyUserName = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
            _SQLSelectByKeyUserName = _SQLSelectByKeyUserName.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
        {
            string query;

            query = @"SELECT UserName, FullName, Pass, [Create], [Status], [Group], [Address], Phone, Mobile, Email, AutoItem, lockActive, AgentID, IDPOS FROM DM_USER WHERE (UserName = @UserName)";
            _SQLSelectByKey = query;

            query = @"SELECT UserName, FullName, Pass, [Create], [Status], [Group], [Address], Phone, Mobile, Email, AutoItem, lockActive, AgentID, IDPOS FROM DM_USER WHERE (UserName = @UserName) and (Pass = @Pass)";
            _SQLSelectByKeyUserName = query;

            query = @"UPDATE DM_USER SET FullName = @FullName, Pass = @Pass, [Create] = @Create, [Status] = @Status, [Group] = @Group, [Address] = @Address, Phone = @Phone, Mobile = @Mobile, Email = @Email, AutoItem = @AutoItem, lockActive = @lockActive, AgentID = @AgentID, IDPOS = @IDPOS WHERE (UserName = @UserName)";
            _SQLUpdate = query;

            query = @"INSERT INTO DM_USER VALUES (@UserName, @FullName, @Pass, @Create, @Status, @Group, @Address, @Phone, @Mobile, @Email, @AutoItem, @lockActive, @AgentID, @IDPOS)";
            _SQLInsert = query;

            query = @"DELETE FROM DM_USER WHERE (UserName = @UserName)";
            _SQLDelete = query;

            query = @"SELECT UserName, FullName, Pass, [Create], [Status], [Group], [Address], Phone, Mobile, Email, AutoItem, lockActive, AgentID, IDPOS FROM DM_USER";
            _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(DM_USER obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);
                DalTools.addDbDataParameter(command, "FullName", obj.FullName, DbType.String);
                DalTools.addDbDataParameter(command, "Pass", obj.Pass, DbType.String);
                DalTools.addDbDataParameter(command, "Create", obj.Create, DbType.DateTime);
                DalTools.addDbDataParameter(command, "Status", obj.Status, DbType.Int16);
                DalTools.addDbDataParameter(command, "Group", obj.Group, DbType.String);
                DalTools.addDbDataParameter(command, "Address", obj.Address, DbType.String);
                DalTools.addDbDataParameter(command, "Phone", obj.Phone, DbType.String);
                DalTools.addDbDataParameter(command, "Mobile", obj.Mobile, DbType.String);
                DalTools.addDbDataParameter(command, "Email", obj.Email, DbType.String);
                DalTools.addDbDataParameter(command, "AutoItem", obj.AutoItem, DbType.Int32);
                DalTools.addDbDataParameter(command, "lockActive", obj.lockActive, DbType.Int16);
                DalTools.addDbDataParameter(command, "AgentID", obj.AgentID, DbType.Int32);
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(DM_USER obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(DM_USER obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(DM_USER obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties

        private DM_USERList SelectListByCommand(IDbCommand command)
        {
            DM_USERList list = new DM_USERList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                DM_USER obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new DM_USER();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private DM_USERList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            DM_USERList list = new DM_USERList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                DM_USER obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new DM_USER();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ReadProperties(DM_USER obj, IDataReader reader)
        {
            try
            {
                if (!(reader["UserName"] is DBNull))
                {
                    obj.UserName = (string)reader["UserName"];
                }
                if (!(reader["FullName"] is DBNull))
                {
                    obj.FullName = (string)reader["FullName"];
                }
                if (!(reader["Pass"] is DBNull))
                {
                    obj.Pass = (string)reader["Pass"];
                }
                if (!(reader["Create"] is DBNull))
                {
                    obj.Create = (DateTime)reader["Create"];
                }
                if (!(reader["Status"] is DBNull))
                {
                    obj.Status = (Int16)reader["Status"];
                }
                if (!(reader["Group"] is DBNull))
                {
                    obj.Group = (string)reader["Group"];
                }
                if (!(reader["Address"] is DBNull))
                {
                    obj.Address = (string)reader["Address"];
                }
                if (!(reader["Phone"] is DBNull))
                {
                    obj.Phone = (string)reader["Phone"];
                }
                if (!(reader["Mobile"] is DBNull))
                {
                    obj.Mobile = (string)reader["Mobile"];
                }
                if (!(reader["Email"] is DBNull))
                {
                    obj.Email = (string)reader["Email"];
                }
                if (!(reader["AutoItem"] is DBNull))
                {
                    obj.AutoItem = (Int32)reader["AutoItem"];
                }
                if (!(reader["lockActive"] is DBNull))
                {
                    obj.lockActive = (short)reader["lockActive"];
                }
                if (!(reader["AgentID"] is DBNull))
                {
                    obj.AgentID = (Int32)reader["AgentID"];
                }
                if (!(reader["IDPOS"] is DBNull))
                {
                    obj.IDPOS = (string)reader["IDPOS"];
                }
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion

        public DM_USER SelectByKey(string UserName)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "UserName", UserName, DbType.String);
            DM_USERList list = SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_USER SelectGetLogin(string UserName, string Pass)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKeyUserName;
            DalTools.addDbDataParameter(command, "UserName", UserName, DbType.String);
            DalTools.addDbDataParameter(command, "Pass", TCEncryption.ToEncrypt(Pass), DbType.String);
            DM_USERList list = SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            else
            {
                DM_GROUPDao executeGROUP = new DM_GROUPDao();
                string Group = list[0].Group;
                if (Group != "")
                {
                    string str = "";
                    Group = Group.Replace("\"", "").ToString();
                    int i = Int32.Parse(Group);
                    DM_GROUP objGroup = executeGROUP.SelectByKey(i);
                    if (objGroup != null)
                    {
                        list[0].DM_GROUP = objGroup;
                        str += objGroup.GPer + ",";
                        str = str.Remove(str.Length - 1, 1).Replace("\"", "");

                        StaticVar.CurrUSER = list[0];
                        string[] Array = str.Split(',');
                        StaticVar.ListPermission = new System.Collections.Generic.List<string>();
                        StaticVar.ListPermission.AddRange(Array);
                    }
                    else
                    {
                        list[0].DM_GROUP = null;

                        StaticVar.CurrUSER = list[0];
                    }
                }
            }
            return list[0];
        }

        public DM_USER SelectByKey(string UserName, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "UserName", UserName, DbType.String);
            DM_USERList list = SelectListByCommand(command, trans);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_USERList SelectAll()
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            DM_USERList list = SelectListByCommand(command);
            return list;
        }
        public DM_USERList SelectAll(IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            DM_USERList list = SelectListByCommand(command, trans);
            return list;
        }
        public DM_USERList SelectByCommand(string Command)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = Command;
            DM_USERList list = SelectListByCommand(command);
            return list;
        }

        public DM_USERList SelectByCondition(DM_USER obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_USER " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_USERList list = SelectListByCommand(command, trans);
            return list;
        }

        public DM_USERList SelectByCondition(DM_USER obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_USER " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_USERList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(DM_USER obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM DM_USER " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(DM_USER obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            if (obj.IsSetUserName)
            {
                s += " AND UserName = @UserName ";
            }
            if (obj.IsSetFullName)
            {
                s += " AND FullName = @FullName ";
            }
            if (obj.IsSetPass)
            {
                s += " AND Pass = @Pass ";
            }
            if (obj.IsSetCreate)
            {
                s += " AND Create = @Create ";
            }
            if (obj.IsSetStatus)
            {
                s += " AND Status = @Status ";
            }
            if (obj.IsSetGroup)
            {
                s += " AND Group = @Group ";
            }
            if (obj.IsSetAddress)
            {
                s += " AND Address = @Address ";
            }
            if (obj.IsSetPhone)
            {
                s += " AND Phone = @Phone ";
            }
            if (obj.IsSetMobile)
            {
                s += " AND Mobile = @Mobile ";
            }
            if (obj.IsSetEmail)
            {
                s += " AND Email = @Email ";
            }
            if (obj.IsSetAutoItem)
            {
                s += " AND AutoItem = @AutoItem ";
            }
            if (obj.IsSetLockActive)
            {
                s += " AND lockActive = @lockActive ";
            }
            if (obj.IsSetAgentID)
            {
                s += " AND AgentID = @AgentID ";
            }
            if (obj.IsSetIDPOS)
            {
                s += " AND IDPOS = @IDPOS ";
            }
            if (s.Length > 0)
            {
                s = " WHERE " + s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, DM_USER obj)
        {
            if (obj == null) { return; }
            if (obj.IsSetUserName)
            {
                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);
            }
            if (obj.IsSetFullName)
            {
                DalTools.addDbDataParameter(command, "FullName", obj.FullName, DbType.String);
            }
            if (obj.IsSetPass)
            {
                DalTools.addDbDataParameter(command, "Pass", obj.Pass, DbType.String);
            }
            if (obj.IsSetCreate)
            {
                DalTools.addDbDataParameter(command, "Create", obj.Create, DbType.DateTime);
            }
            if (obj.IsSetStatus)
            {
                DalTools.addDbDataParameter(command, "Status", obj.Status, DbType.Int16);
            }
            if (obj.IsSetGroup)
            {
                DalTools.addDbDataParameter(command, "Group", obj.Group, DbType.String);
            }
            if (obj.IsSetAddress)
            {
                DalTools.addDbDataParameter(command, "Address", obj.Address, DbType.String);
            }
            if (obj.IsSetPhone)
            {
                DalTools.addDbDataParameter(command, "Phone", obj.Phone, DbType.String);
            }
            if (obj.IsSetMobile)
            {
                DalTools.addDbDataParameter(command, "Mobile", obj.Mobile, DbType.String);
            }
            if (obj.IsSetEmail)
            {
                DalTools.addDbDataParameter(command, "Email", obj.Email, DbType.String);
            }
            if (obj.IsSetAutoItem)
            {
                DalTools.addDbDataParameter(command, "AutoItem", obj.AutoItem, DbType.Int32);
            }
            if (obj.IsSetLockActive)
            {
                DalTools.addDbDataParameter(command, "lockActive", obj.lockActive, DbType.Int16);
            }
            if (obj.IsSetAgentID)
            {
                DalTools.addDbDataParameter(command, "AgentID", obj.AgentID, DbType.Int32);
            }
            if (obj.IsSetIDPOS)
            {
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);
            }
        }
        #endregion
    }
}